<?php
class M_Article extends Apps_Db_Table_Abstract {
    protected $_name = 'article';

    const STATUS_ACTIVE   = 1;
    const STATUS_INACTIVE = 0;
    const STATUS_DELETED  = -1;

    const REFERENCE_TYPE_TOUR 	= 1;
    const REFERENCE_TYPE_HOTEL 	= 2;
    const REFERENCE_TYPE_CRUISE = 3;
    const REFERENCE_TYPE_EVENT 	= 4;

    const REFERENCE_TYPE_NOMAL 	= 7;
    const REFERENCE_TYPE_ABOUT 	= 8;
    const REFERENCE_TYPE_VISA 	= 9;
    const REFERENCE_TYPE_COUNTRY 	= 12;

    const REFERENCE_TRAVEL_GUIDE  	= 15;
    const REFERENCE_BOOKING_GUIDE  	= 16;
    const REFERENCE_FAQS  			= 17;
    const REFERENCE_VCT_HISTORY  	= 18;
    const REFERENCE_WHY_VCT			= 19;
    const REFERENCE_CAREER			= 20;

    // Use for Country article
    const COUNTRY_ARTICLE_TYPE_GENERAL_INFORMATION  = 1;
    const COUNTRY_ARTICLE_TYPE_TRAVEL_DESTINATION	= 2;
    const COUNTRY_ARTICLE_TYPE_TRAVEL_NEWS			= 3;
    const COUNTRY_ARTICLE_TYPE_TRAVEL_TIPS 			= 4;

    public function getArticleByReference ($referenceId, $referenceType) {
        $q = $this->_db
                  ->select()
                  ->from('article', null)
                  ->where('reference_id =?', $referenceId)
                  ->where('reference_type =?', $referenceType)
                  ->where('status =?', self::STATUS_ACTIVE)
                  ->columns(array('id' => 'article.id',
                  				  'thumbnail' => 'article.thumbnail'));

        if($this->getLang() == 'en') {
            $q->columns(array('title'     => 'article.en_title',
                              'detail'    => 'article.en_detail'));
        } else $q->columns(array('title'  => 'article.title',
                                 'detail' => 'article.detail'));

        return $q;
    }


    //get artilce by type: visa, about, normal
    public function getArticleByType($type) {
        $q = $this->_db
                  ->select()
                  ->from('article', null)
                  ->where('reference_type =?', $type)
                  ->where('status =?', self::STATUS_ACTIVE)
                  ->order('id DESC')
                  ->columns(array('id' => 'article.id',
                  				  'thumbnail' => 'article.thumbnail'));

        if($this->getLang() == 'en') {
            $q->columns(array('title'       => 'article.en_title',
                              'detail'      => 'article.en_detail',
                              'overview'    => 'article.en_overview'));
        } else $q->columns(array('title'    => 'article.title',
                                 'detail'   => 'article.detail',
                                 'overview' => 'article.overview'));
        return $q;
    }

    public function getArticleById($articleId) {
        $q = $this->_db
                  ->select()
                  ->from('article', null)
                  ->where('status = ? ', self::STATUS_ACTIVE)
                  ->where('id = ? ', $articleId)
                  ->columns(array('id' 				=> 'article.id',
                  				  'reference_type' 	=> 'article.reference_type'));

        if($this->getLang() == 'en') {
            $q->columns(array('title'       => 'article.en_title',
                              'detail'      => 'article.en_detail',
                              'overview'    => 'article.en_overview'));
        } else $q->columns(array('title'    => 'article.title',
                                 'detail'   => 'article.detail',
                                 'overview' => 'article.overview'));
        return $q;
    }

	/*
	 * if isset $articleId : Select Article != $articleId
	 * */
    public function getMultiArticleByType($type, $articleId = null) {
        $q = $this->_db
                  ->select()
                  ->from('article', null)
                  ->where('reference_type = ?', $type)
                  ->where('status = ?', self::STATUS_ACTIVE)
                  ->columns(array('id' => 'article.id'));

        if($articleId != null) {
			$q->where('id != ?', $articleId);
        }

        if($this->getLang() == 'en') {
            $q->columns(array('title'       => 'article.en_title',
                              'detail'      => 'article.en_detail',
                              'overview'    => 'article.en_overview'));
        } else $q->columns(array('title'    => 'article.title',
                                 'detail'   => 'article.detail',
                                 'overview' => 'article.overview'));
        return $q;
    }


    //COUNTRY ARTICLE
    public function listCountryArticle($countryId , $type = null) {
    	$q = $this->_db
			    	->select()
			    	->from('article', null)
			    	->join('country', 'country.id = article.reference_id', null)
			    	->where('reference_type = ?', self::REFERENCE_TYPE_COUNTRY)
			    	->where('country.id = ?', $countryId)
			    	->columns(array(
			    			'id' 		 	=> 'article.id',
			    			'country_id' 	=> 'country.id',
			    			'type'		 	=> 'article.type',
			    			'thumbnail'		=> 'article.thumbnail',
			    			'description'	=> 'article.description',
			    			'created_date'	=> 'article.created_date'
			    	));

    	if($type) {
    		$q->where('type =?' , $type);
    	}

    	if($this->getLang() == 'en') {
    		$q->columns(array(
    				'title' 		=> 'article.en_title',
    				'overview' 		=> 'article.en_overview',
    				'country_name'	=> 'country.en_name'));
    	} else {
    		$q->columns(array(
    				'title' => 'title',
    				'overview' => 'overview',
    				'country_name'	=> 'country.name'));
    	}
    	return $q;
    }

    public function getCountryArticleById($id) {
    	$q = $this->_db
			    	->select()
			    	->from('article', null)
			    	->join('country', 'country.id = article.reference_id', null)
			    	->where('article.status =? ', 1)
			    	->where('article.id =? ', $this->_db->quote($id))
			    	->columns(array('id'                  => 'article.id',
			    			'country_id'          => 'country.id',
			    			'thumbnail'           => 'article.thumbnail'));

    	if($this->getLang() == 'en') {
    		$q->columns(array('title'            => 'article.en_title',
		    				'overview'         => 'article.en_overview',
		    				'detail'           => 'article.en_detail',
		    				'country_name'     => 'country.en_name'));
    	} else {
    		$q->columns(array('title'            => 'article.title',
		    				'overview'         => 'article.overview',
		    				'detail'           => 'article.detail',
		    				'country_name'     => 'country.name'));
    	}
    	return $q;
    }

    public function listOtherCountryArticle($countryId , $type = null, $id) {
    	$q = $this->_db
			    	->select()
			    	->from('article', null)
			    	->join('country', 'country.id = article.reference_id', null)
			    	->where('reference_type = ?', self::REFERENCE_TYPE_COUNTRY)
			    	->where('country.id = ?', $countryId)
			    	->where('article.id != ?', $id)
			    	->columns(array(
			    			'id' 		 	=> 'article.id',
			    			'country_id' 	=> 'country.id',
			    			'type'		 	=> 'article.type',
			    	));

    	if($type) {
    		$q->where('type =?' , $type);
    	}

    	if($this->getLang() == 'en') {
    		$q->columns(array(
    				'title' 		=> 'article.en_title',
    				'country_name'	=> 'country.en_name'));
    	} else {
    		$q->columns(array(
    				'title' => 'title',
    				'country_name'	=> 'country.name'));
    	}
    	return $q;
    }
    
    //Get travel destination article : #Vn
    public function getArticleTravelDestinationByCountryId ($countryId) {
        $q = $this->_db
                  ->select()
                  ->from('article', null)
                  ->where('reference_id =?', $countryId)
                  ->where('reference_type =?', self::REFERENCE_TYPE_COUNTRY)
                  ->where('article.type =?', self::COUNTRY_ARTICLE_TYPE_TRAVEL_DESTINATION)
                  ->where('status =?', self::STATUS_ACTIVE)
                  ->order('article.created_date DESC')
                  ->columns(array('id' => 'article.id'));

        if($this->getLang() == 'en') {
            $q->columns(array('title'     => 'article.en_title',
                              'overview'  => 'article.en_overview',
                              'detail'    => 'article.en_detail'));
        } else $q->columns(array('title'  => 'article.title',
                              'overview'  => 'article.overview',
                                 'detail' => 'article.detail'));
        return $q;
    }
    //list other article travel destination 
    public function listOtherArticleTravelDestinationByCountryId ($countryId, $id) {
        $q = $this->_db
                  ->select()
                  ->from('article', null)
                  ->where('reference_id =?', $countryId)
                  ->where('reference_type =?', self::REFERENCE_TYPE_COUNTRY)
                  ->where('article.type =?', self::COUNTRY_ARTICLE_TYPE_TRAVEL_DESTINATION)
                  ->where('article.id !=?', $id)
                  ->where('status =?', self::STATUS_ACTIVE)
                  ->order('article.created_date DESC')
                  ->columns(array('id' => 'article.id'));

        if($this->getLang() == 'en') {
            $q->columns(array('title'     => 'article.en_title',
                              'overview'  => 'article.en_overview',
                              'detail'    => 'article.en_detail'));
        } else $q->columns(array('title'  => 'article.title',
                              'overview'  => 'article.overview',
                                 'detail' => 'article.detail'));
        return $q;
    }

    //REGION ARTICLE
    public function listRegionArticle($countryId , $type = null) {
    	$q = $this->_db
			    	->select()
			    	->from('article', null)
			    	->join('region', 'region.id = article.reference_id', null)
			    	->join('country', 'country.id = region.country_id', null)
			    	->where('reference_type = ?', M_DbTable_Article::REFERENCE_TYPE_REGION)
			    	->where('country.id = ?', $countryId)
			    	->columns(array(
			    			'id' 		 	=> 'article.id',
			    			'country_id' 	=> 'country.id',
			    			'region_id'		=> 'region.id',
			    			'type'		 	=> 'article.type',
			    			'thumbnail'		=> 'article.thumbnail',
			    			'description'	=> 'article.description',
			    			'created_date'	=> 'article.created_date'
			    	));

    	if($type) {
    		$q->where('type =?' , $type);
    	}

    	if($this->getLang() == 'en') {
    		$q->columns(array(
    				'title' 		=> 'article.en_title',
    				'overview' 		=> 'article.en_overview',
    				'region_name'	=> 'region.en_name',
    				'country_name'	=> 'country.en_name'));
    	} else {
    		$q->columns(array(
    				'title' 		=> 'title',
    				'overview' 		=> 'overview',
    				'region_name'	=> 'region.name',
    				'country_name'	=> 'country.name'));
    	}
    	return $q;
    }

    // Region
    public function getRegionArticleById($id) {
    	$q = $this->_db
			    	->select()
			    	->from('article', null)
			    	->join('region', 'region.id = article.reference_id', null)
			    	->join('country', 'country.id = region.country_id', null)
			    	->where('article.status =? ', 1)
			    	->where('article.id =? ', $this->_db->quote($id))
			    	->columns(array('id'			=> 'article.id',
					    			'region_id' 	=> 'region.id',
			    					'country_id'	=> 'country.id',
					    			'thumbnail' 	=> 'article.thumbnail'));

    	if($this->getLang() == 'en') {
    		$q->columns(array('title'           => 'article.en_title',
		    				  'overview'        => 'article.en_overview',
		    				  'detail'          => 'article.en_detail',
    						  'country_name'	=> 'country.en_name',
		    				  'region_name'     => 'region.en_name'));
    	} else {
    		$q->columns(array('title'           => 'article.title',
		    				  'overview'        => 'article.overview',
		    				  'detail'          => 'article.detail',
    						  'country_name'	=> 'country.name',
		    				  'region_name'     => 'region.name'));
    	}
    	return $q;
    }

    public function listOtherRegionArticle($regionId, $countryId, $type = null, $id) {
    	$q = $this->_db
			    	->select()
			    	->from('article', null)
			    	->join('region', 'region.id = article.reference_id', null)
			    	->where('reference_type = ?', M_DbTable_Article::REFERENCE_TYPE_REGION)
			    	->where('region.country_id = ?', $countryId)
			    	->where('article.id != ?', $id)
			    	->columns(array(
			    			'id' 		 	=> 'article.id',
			    			'region_id' 	=> 'region.id',
			    			'type'		 	=> 'article.type',
			    	));

    	if($type) {
    		$q->where('type =?' , $type);
    	}

    	if($this->getLang() == 'en') {
    		$q->columns(array(
    				'title' 		=> 'article.en_title',
    				'region_name'	=> 'region.en_name'));
    	} else {
    		$q->columns(array(
    				'title' => 'title',
    				'region_name'	=> 'region.name'));
    	}

    	return $q;
    }
    
    //List other article by reference type
    public function listOtherArticle($type, $id) {
    	$q = $this->_db
			    	->select()
			    	->from('article', null)
			    	->where('article.reference_type =? ', $type)
			    	->where('article.status =? ', 1)
			    	->where('article.id !=? ', $id)
			    	->order('article.sorting_number ASC')
			    	->columns(array('id' => 'article.id'));
    
    	if($this->getLang() == 'en') {
    		$q->columns(array('title'        => 'article.en_title'));
    	} else $q->columns(array('title'     => 'article.title'));
    
    	return $q;
    }
    
}